﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using TM.Common.Extensions;
using TM.Data.Contracts;
using TM.DataAccessor;
using TM.ExceptionManager;
using TM.Model;

namespace TM.Data
{
    public class ProjectRepository : IProjectRepository
    {
        private IDataAccessor _dataAccessor;
        private ITMExceptionHandler _exceptionHandler;

        public ProjectRepository(IIoCParams ioCParams)
        {
            _dataAccessor = ioCParams.DataAccessor;
            _exceptionHandler = ioCParams.ExceptionHandler;
#if DEBUG
            _exceptionHandler.Throwable = true;
#endif
        }

        #region Implementation of IRepository<Project>

        public IQueryable<Project> GetAll()
        {


            return MapperFactories.GetMapper<Project>().Map(GetAllAsDataTable()).AsQueryable();
        }

        public DataTable GetAllAsDataTable()
        {
            _dataAccessor.Command.CommandType = CommandType.Text;
            _dataAccessor.Command.CommandText =
                "SELECT [ID],[Name],datetime([EstimatedStart], 'unixepoch') AS EstimatedStart, " +
                "datetime([ActualStart], 'unixepoch') AS ActualStart,[Duration],[ProjectOwnerID],[Description]," +
                "datetime([CreatedDate], 'unixepoch') AS CreatedDate FROM Project";
            var dt = _dataAccessor.ExecuteReader();

            return dt;
        }

        public Project GetById(Guid id)
        {
            return MapperFactories.GetMapper<Project>().Map(GetByIdAsDataTable(id))
                .AsQueryable().FirstOrDefault();
        }

        public DataTable GetByIdAsDataTable(Guid id)
        {
            _dataAccessor.Command.CommandType = CommandType.Text;
            _dataAccessor.Command.CommandText =string.Format("SELECT [ID],[Name],datetime([EstimatedStart], 'unixepoch') AS EstimatedStart, " +
                "datetime([ActualStart], 'unixepoch') AS ActualStart,[Duration],[ProjectOwnerID],[Description]," +
                "datetime([CreatedDate], 'unixepoch') AS CreatedDate FROM Project WHERE UPPER([ID]) = UPPER('{0}')", id.ToString());
            var dt = _dataAccessor.ExecuteReader();

            return dt;
        }

        public IQueryable<Project> GetList(Func<Project, bool> predicate)
        {
            var data = GetAll();
            return from item in data where predicate(item) select item;
        }

        public Project GetSingle(Func<Project, bool> predicate)
        {
            var data = GetAll();
            return (from item in data select item).Single(predicate);
        }

        public Project GetFisrt(Func<Project, bool> predicate)
        {
            var data = GetAll();
            return (from item in data select item).First(predicate);
        }

        public bool Insert(Project entity)
        {
            try
            {
                _dataAccessor.Command.CommandType = CommandType.Text;
                _dataAccessor.Command.CommandText =
                    string.Format("INSERT INTO Project([ID],[Name],[EstimatedStart], " +
                                  "[ActualStart], [Duration], [ProjectOwnerId], [Description], " +
                                  "[CreatedDate]) VALUES({0},'{1}',{2},{3}, {4} , {5} ,'{6}', {7})",
                                  entity.ID, entity.Name, entity.EstimatedStart.ToSQLiteDateTime(),
                                  entity.ActualStart.ToSQLiteDateTime(),
                                  entity.Duration.GetValueOrDefault(),
                                  entity.ProjectOwnerId, 
                                  entity.Description,
                                  DateTime.Now.ToSQLiteDateTime());

                var rt = _dataAccessor.ExecuteNonQuery();

                return (rt > 0);
            }
            catch (Exception exception)
            {
                _exceptionHandler.Handle(exception, "Insert Project item");
            }
            return false;
        }

        public bool Insert(IList<Project> entities)
        {
            var trans = _dataAccessor.BeginTransaction("InsertProjects");

            try
            {
                foreach (var entity in entities)
                {
                    Insert(entity);
                }
                _dataAccessor.EndTransaction(trans, true);
            }
            catch (Exception exception)
            {
                _dataAccessor.EndTransaction(trans, false);
                _exceptionHandler.Handle(exception, "Insert list of Projects");
            }

            return false;
        }

        public bool Update(Project entity)
        {
            try
            {
                _dataAccessor.Command.CommandType = CommandType.Text;
                _dataAccessor.Command.CommandText =
                    string.Format("UPDATE Project SET" +
                                  " [Name] = '{1}',[EstimatedStart] = {2}, [ActualStart] = {3}, [Duration] = {4}, " +
                                  "[ProjectOwnerId] = {5}, [Description] = '{6}' WHERE UPPER([ID]) = UPPER('{0}')",
                                  entity.ID, entity.Name, entity.EstimatedStart.ToSQLiteDateTime(),
                                  entity.ActualStart.ToSQLiteDateTime(),
                                  entity.Duration.GetValueOrDefault(),
                                  entity.ProjectOwnerId, entity.Description);

                var rt = _dataAccessor.ExecuteNonQuery();

                return (rt > 0);
            }
            catch (Exception exception)
            {
                _exceptionHandler.Handle(exception, "Update Project item");
            }
            return false;
        }

        public bool Update(IList<Project> entities)
        {
            var trans = _dataAccessor.BeginTransaction("UpdateProjects");

            try
            {
                foreach (var entity in entities)
                {
                    Update(entity);
                }
                _dataAccessor.EndTransaction(trans, true);
            }
            catch (Exception exception)
            {
                _dataAccessor.EndTransaction(trans, false);
                _exceptionHandler.Handle(exception, "Update list of Projects");
            }

            return false;
        }

        public bool Delete(Project entity)
        {
            try
            {
                _dataAccessor.Command.CommandType = CommandType.Text;
                _dataAccessor.Command.CommandText =
                    string.Format("DELETE FROM Project WHERE UPPER([ID]) = UPPER('{0}')",
                                  entity.ID);

                var rt = _dataAccessor.ExecuteNonQuery();

                return (rt > 0);
            }
            catch (Exception exception)
            {
                _exceptionHandler.Handle(exception, "Delete Project item");
            }
            return false;
        }

        public bool Delete(IList<Project> entities)
        {
            var trans = _dataAccessor.BeginTransaction("DeleteProjects");
            try
            {
                foreach (var entity in entities)
                {
                    _dataAccessor.Command.CommandType = CommandType.Text;
                    _dataAccessor.Command.CommandText =
                        string.Format("DELETE FROM Project WHERE UPPER([ID]) = UPPER('{0}')",
                                      entity.ID);

                    var rt = _dataAccessor.ExecuteNonQuery();    
                    if(!(rt > 0))
                    {
                        _dataAccessor.EndTransaction(trans, false);
                    }
                }
                _dataAccessor.EndTransaction(trans, true);
            }
            catch (Exception exception)
            {
                _dataAccessor.EndTransaction(trans, false);
                _exceptionHandler.Handle(exception, "Delete Project item");
            }
            return false;
        }

        public bool Delete(Guid id)
        {
            try
            {
                _dataAccessor.Command.CommandType = CommandType.Text;
                _dataAccessor.Command.CommandText =
                    string.Format("DELETE FROM Project WHERE UPPER([ID]) = UPPER('{0}')",
                                  id);

                var rt = _dataAccessor.ExecuteNonQuery();

                return (rt > 0);
            }
            catch (Exception exception)
            {
                _exceptionHandler.Handle(exception, "Delete Project item");
            }
            return false;
        }

        public bool Delete(Func<Project, bool> predicate)
        {
            var items = GetList(predicate);
            return Delete(items.ToList());
        }

        #endregion

        #region Implementation of IProjectRepository

        public IQueryable<Project> GetAllOfTheOwner(int ownerId)
        {
            _dataAccessor.Command.CommandType = CommandType.Text;
            _dataAccessor.Command.CommandText = "SELECT [ID],[Name],datetime([EstimatedStart], 'unixepoch') AS EstimatedStart, " +
                "datetime([ActualStart], 'unixepoch') AS ActualStart,[Duration],[ProjectOwnerID],[Description]," +
                "datetime([CreatedDate], 'unixepoch') AS CreatedDate FROM Project WHERE UPPER([ProjectOwnerID]) = UPPER('{0}')" + ownerId;
            var dt = _dataAccessor.ExecuteReader();

            return MapperFactories.GetMapper<Project>().Map(dt).AsQueryable();
        }

        public IQueryable<Project> GetAllOfTheOwner(int ownerId, DateTime startDate)
        {
            var from = new DateTime(startDate.Year, startDate.Month, startDate.Day, 0, 0, 0);
            var to = new DateTime(startDate.Year, startDate.Month, startDate.Day, 23, 59, 59);

            _dataAccessor.Command.CommandType = CommandType.Text;
            _dataAccessor.Command.CommandText =
                string.Format("SELECT [ID],[Name],datetime([EstimatedStart], 'unixepoch') AS EstimatedStart, " +
                              "datetime([ActualStart], 'unixepoch') AS ActualStart,[Duration],[ProjectOwnerID],[Description]," +
                              "datetime([CreatedDate], 'unixepoch') AS CreatedDate FROM Project WHERE UPPER([ProjectOwnerID]) = UPPER('{0}') AND " +
                              "[EstimatedStart] >= {1} AND [EstimatedStart]<= {2}", ownerId, from.ToSQLiteDateTime(),
                              to.ToSQLiteDateTime());
            var dt = _dataAccessor.ExecuteReader();

            return MapperFactories.GetMapper<Project>().Map(dt).AsQueryable();
        }

        public IQueryable<Project> GetAllOfTheOwnerByCreationDate(int ownerId, DateTime createdDate)
        {
            var from = new DateTime(createdDate.Year, createdDate.Month, createdDate.Day, 0, 0, 0);
            var to = new DateTime(createdDate.Year, createdDate.Month, createdDate.Day, 23, 59, 59);

            _dataAccessor.Command.CommandType = CommandType.Text;
            _dataAccessor.Command.CommandText =
                string.Format("SELECT [ID],[Name],datetime([EstimatedStart], 'unixepoch') AS EstimatedStart, " +
                              "datetime([ActualStart], 'unixepoch') AS ActualStart,[Duration],[ProjectOwnerID],[Description]," +
                              "datetime([CreatedDate], 'unixepoch') AS CreatedDate FROM Project WHERE UPPER([ProjectOwnerID]) = UPPER('{0}') AND " +
                              "[CreatedDate] >= {1} AND [CreatedDate]<= {2}", ownerId, from.ToSQLiteDateTime(),
                              to.ToSQLiteDateTime());
            var dt = _dataAccessor.ExecuteReader();

            return MapperFactories.GetMapper<Project>().Map(dt).AsQueryable();
        }

        public IQueryable<Project> GetAllOfTheOwnerByActualStartDate(int ownerId, DateTime actualStartDate)
        {
            var from = new DateTime(actualStartDate.Year, actualStartDate.Month, actualStartDate.Day, 0, 0, 0);
            var to = new DateTime(actualStartDate.Year, actualStartDate.Month, actualStartDate.Day, 23, 59, 59);

            _dataAccessor.Command.CommandType = CommandType.Text;
            _dataAccessor.Command.CommandText =
                string.Format("SELECT [ID],[Name],datetime([EstimatedStart], 'unixepoch') AS EstimatedStart, " +
                              "datetime([ActualStart], 'unixepoch') AS ActualStart,[Duration],[ProjectOwnerID],[Description]," +
                              "datetime([CreatedDate], 'unixepoch') AS CreatedDate FROM Project WHERE UPPER([ProjectOwnerID]) = UPPER('{0}') AND " +
                              "[ActualStart] >= {1} AND [ActualStart]<= {2}", ownerId, from.ToSQLiteDateTime(),
                              to.ToSQLiteDateTime());
            var dt = _dataAccessor.ExecuteReader();

            return MapperFactories.GetMapper<Project>().Map(dt).AsQueryable();
        }

        #endregion
    }
}
